{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8b6eb478",
   "metadata": {},
   "source": [
    "| [02_advanced/02_数据库_sql.ipynb](https://github.com/shibing624/python-tutorial/blob/master/02_advanced/02_数据库_sql.ipynb)  | Python操作mysql数据库  |[Open In Colab](https://colab.research.google.com/github/shibing624/python-tutorial/blob/master/02_advanced/02_数据库_sql.ipynb) |\n",
    "\n",
    "# 数据库\n",
    "\n",
    "\n",
    "无论是Google、Facebook，还是国内的BAT，无一例外都选择了免费的开源数据库：\n",
    "\n",
    "- MySQL，大家都在用，一般错不了；\n",
    "- PostgreSQL，学术气息有点重，其实挺不错，但知名度没有MySQL高；\n",
    "- sqlite，嵌入式数据库，适合桌面和移动应用。\n",
    "\n",
    "作为Python开发工程师，选择哪个免费数据库呢？一般是MySQL，测试或本地开发使用sqlite也可以。\n",
    "\n",
    "## SQLite\n",
    "\n",
    "SQLite是一种嵌入式数据库，它的数据库就是一个文件。\n",
    "\n",
    "Python就内置了SQLite3，所以，在Python中使用SQLite，不需要安装任何东西，直接使用。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fb7c81d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入SQLite驱动:\n",
    "import sqlite3\n",
    "# 连接到SQLite数据库\n",
    "# 数据库文件是test.db\n",
    "# 如果文件不存在，会自动在当前目录创建:\n",
    "\n",
    "import os\n",
    "os.remove('test.db')\n",
    "conn = sqlite3.connect('test.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dedd5e43",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建一个Cursor:\n",
    "cursor = conn.cursor()\n",
    "# 执行一条SQL语句，创建user表:\n",
    "cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')\n",
    "# <sqlite3.Cursor object at 0x10f8aa260>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "288b4e27",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 继续执行一条SQL语句，插入一条记录:\n",
    "cursor.execute('insert into user (id, name) values (\\'1\\', \\'Michael\\')')\n",
    "# <sqlite3.Cursor object at 0x10f8aa260>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dccbc415",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 通过rowcount获得插入的行数:\n",
    "cursor.rowcount\n",
    "# 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb402ed6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 关闭Cursor:\n",
    "cursor.close()\n",
    "# 提交事务:\n",
    "conn.commit()\n",
    "# 关闭Connection:\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa396d33",
   "metadata": {},
   "source": [
    "我们再试试查询记录：\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a667ce19",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('1', 'Michael')]\n"
     ]
    }
   ],
   "source": [
    "conn = sqlite3.connect('test.db')\n",
    "cursor = conn.cursor()\n",
    "# 执行查询语句:\n",
    "cursor.execute('select * from user where id=?', '1')\n",
    "# 获得查询结果集:\n",
    "values = cursor.fetchall()\n",
    "print(values)\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8d122f04",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.remove('test.db') # 删除数据库文件"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc9c0efb",
   "metadata": {},
   "source": [
    "## MySQL\n",
    "\n",
    "MySQL是Web世界中使用最广泛的数据库服务器。SQLite的特点是轻量级、可嵌入，但不能承受高并发访问，适合桌面和移动应用。而MySQL是为服务器端设计的数据库，能承受高并发访问，同时占用的内存也远远大于SQLite。\n",
    "\n",
    "可以直接从MySQL官方网站下载最新的[Community Server 5.6.x版本](https://dev.mysql.com/downloads/mysql/5.6.html)。\n",
    "\n",
    "### PyMySQL\n",
    "\n",
    "PyMySQL是一个纯Python写的MySQL操作库，它的目标是替代MySQLdb，PyMySQL的性能和MySQLdb几乎相当，如果对性能要求不是特别的强，使用PyMySQL将更加方便。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d024857",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install PyMySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a610154",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "061db4c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "config = {\n",
    "    'host': '127.0.0.1',\n",
    "    'port': 3306,\n",
    "    'user': 'root',\n",
    "    'passwd': '',\n",
    "    'charset': 'utf8mb4',\n",
    "    'cursorclass': pymysql.cursors.DictCursor\n",
    "}\n",
    "conn = pymysql.connect(**config)\n",
    "conn.autocommit(1)\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfd72bc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    # 创建数据库\n",
    "    DB_NAME = 'test'\n",
    "    cursor.execute('DROP DATABASE IF EXISTS %s' % DB_NAME)\n",
    "    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' % DB_NAME)\n",
    "    conn.select_db(DB_NAME)\n",
    "\n",
    "    # 创建表\n",
    "    TABLE_NAME = 'user'\n",
    "    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' % TABLE_NAME)\n",
    "\n",
    "    # 批量插入纪录\n",
    "    values = []\n",
    "    for i in range(20):\n",
    "        values.append((i, 'kk' + str(i)))\n",
    "    cursor.executemany('INSERT INTO user values(%s,%s)', values)\n",
    "\n",
    "    # 查询数据条目\n",
    "    count = cursor.execute('SELECT * FROM %s' % TABLE_NAME)\n",
    "    print('total records:', cursor.rowcount)\n",
    "\n",
    "    # 获取表名信息\n",
    "    desc = cursor.description\n",
    "    print(\"%s %3s\" % (desc[0][0], desc[1][0]))\n",
    "\n",
    "    cursor.scroll(10, mode='absolute')\n",
    "    results = cursor.fetchall()\n",
    "    for result in results:\n",
    "        print(result)\n",
    "\n",
    "except:\n",
    "    import traceback\n",
    "\n",
    "    traceback.print_exc()\n",
    "    # 发生错误时会滚\n",
    "    conn.rollback()\n",
    "finally:\n",
    "    # 关闭游标连接\n",
    "    cursor.close()\n",
    "    # 关闭数据库连接\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ee3403d",
   "metadata": {},
   "source": [
    "本节完。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d2f06dd",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}